package com.ming.javaweb.dao.impl;

import com.ming.javaweb.beans.DTO.StudentDTO;
import com.ming.javaweb.beans.VO.StudentVO;
import com.ming.javaweb.beans.VO.TableVO;
import com.ming.javaweb.beans.entity.AdminDO;
import com.ming.javaweb.beans.entity.StudentDO;
import com.ming.javaweb.dao.StudentDao;
import com.ming.javaweb.util.DateUtil;
import com.ming.javaweb.util.JDBCUtil;
import com.ming.javaweb.util.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    @Override
    public int addStudent(StudentDO studentDO) throws SQLException {
        Connection conn = JDBCUtil.getConn();
        if (conn == null){
            return 0;
        }
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("insert into student(name,no,birthday)");
        stringBuilder.append("values( ?,?,?)");
        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            preparedStatement.setObject(1,studentDO.getName());
            preparedStatement.setObject(2,studentDO.getNo());
            preparedStatement.setObject(3,studentDO.getBirthDay());
//            打印最终执行的sql语句
            System.out.println("addStudent执行的sql："+preparedStatement.toString());
           return (int) preparedStatement.executeLargeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.closePs(preparedStatement);
            JDBCUtil.closeConn(conn);
        }
        return 0;
    }

    @Override
    public TableVO<StudentVO> queryStudentByPage(StudentDTO studentDTO) throws SQLException {
        TableVO<StudentVO> tableVO = new TableVO<>();

        Connection conn = JDBCUtil.getConn();
        if (conn == null){
            return null;
        }
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

//        查询学生列表
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select * from student ");
        appendWhere(studentDTO,stringBuilder);
        stringBuilder.append("order by id  limit ").append(studentDTO.getStart())
                .append(",").append(studentDTO.getPageSize());
        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            if (StringUtil.isNotBlank(studentDTO.getStudentName())){
                preparedStatement.setObject(1,studentDTO.getStudentName());
            }

//            打印最终执行的sql语句
            System.out.println("queryStudentByPage查询学生的sql："+preparedStatement.toString());
            resultSet = preparedStatement.executeQuery();
            List<StudentVO> list = new ArrayList<>();
            while (resultSet.next()){

                StudentVO studentvO = buildStudent(resultSet);
                list.add(studentvO);
            }
//            设置数据
            tableVO.setData(list);
            /**
             * 查询总条数
             */
            stringBuilder.setLength(0);
            stringBuilder.append("select count(*) from student");
            appendWhere(studentDTO,stringBuilder);
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            if (StringUtil.isNotBlank(studentDTO.getStudentName())){
                preparedStatement.setObject(1,studentDTO.getStudentName());
            }
//            打印最终执行的sql语句
            System.out.println("queryStudentByPage查询学生总条数的sql："+preparedStatement.toString());
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                int totalCount = resultSet.getInt(1);
//                设置总条数
                tableVO.setTotalCount(totalCount);
            }
            return tableVO;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.closeResultSet(resultSet);
            JDBCUtil.closePs(preparedStatement);
            JDBCUtil.closeConn(conn);
        }
        return null;
    }

    @Override
    public StudentVO getStudentById(int studentId) throws SQLException {
        Connection conn = JDBCUtil.getConn();
        if (conn == null){
            return null;
        }
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select * from student where id = ? ");
        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            preparedStatement.setObject(1,studentId);
//            打印最终执行的sql语句
            System.out.println("getStudentById执行的sql："+preparedStatement.toString());
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
               return buildStudent(resultSet);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.closeResultSet(resultSet);
            JDBCUtil.closePs(preparedStatement);
            JDBCUtil.closeConn(conn);
        }
        return null;
    }

    @Override
    public int updateStudent(StudentDO studentDO) throws SQLException {
        Connection conn = JDBCUtil.getConn();
        if (conn == null){
            return 0;
        }
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("update student set name=?,no=?,birthday=? ");
        stringBuilder.append("where id=?");
        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            preparedStatement.setObject(1,studentDO.getName());
            preparedStatement.setObject(2,studentDO.getNo());
            preparedStatement.setObject(3,studentDO.getBirthDay());
            preparedStatement.setObject(4,studentDO.getId());
//            打印最终执行的sql语句
            System.out.println("updateStudent执行的sql："+preparedStatement.toString());
            return preparedStatement.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.closePs(preparedStatement);
            JDBCUtil.closeConn(conn);
        }
        return 0;
    }

    @Override
    public int deleteById(int deleteId) throws SQLException {
        Connection conn = JDBCUtil.getConn();
        if (conn == null){
            return 0;
        }
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        StringBuilder sb = new StringBuilder();
        sb.append("delete from student where id = ? ");

        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setObject(1,deleteId);
//            打印最终执行的sql语句
            System.out.println("deleteStudent执行的sql："+ps.toString());
            return ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.closePs(ps);
            JDBCUtil.closeConn(conn);
        }
        return 0;
    }

    private StudentVO buildStudent(ResultSet resultSet) throws SQLException {
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        String no = resultSet.getString("no");
        String birthDay = resultSet.getString("birthDay");
        StudentVO studentvO = new StudentVO();
        studentvO.setId(id);
        studentvO.setName(name);
        studentvO.setNo(no);
        studentvO.setBirthDay(birthDay);

        return studentvO;
    }

    private void appendWhere(StudentDTO studentDTO, StringBuilder stringBuilder) {
        String studentName = studentDTO.getStudentName();
        if (StringUtil.isNotBlank(studentName)){
            stringBuilder.append(" where name = ? ");

        }
    }
}

